﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data;

namespace ChequeManagement.DAL
{
    /// <summary>
    /// this class contain methods for connect to dataBase 
    /// this methods are Insert,Update,FetchAll,Delete,InitialObjectsFromReader,InitialBusinessObjectsFromReader
    /// this class inheritance of BaseDAL
    /// </summary>
    class DALChequeCategoryWithPARI : BaseDAL
    {
        /// <summary>
        /// this constructor called base constructor and set parameter
        /// parameter value is Connectionstring for connect to dataBase
        /// </summary>
        public DALChequeCategoryWithPARI()
            : base(ChequeManagement.Properties.Settings.Default.Database1ConnectionString)
        {

        }

        /// <summary>
        /// this method is for insert a object of BLL.BLLBank to table
        /// this method check connection if connection is open insert object to table
        /// </summary>
        /// <param name="ChequeCategoryPARIObject" Type="BLL.BLLChequeCategoryWithPARI"></param>
        /// <return>this type is void and Do not return</return>
        public void Insert(BLL.BLLChequeCategoryWithPARI ChequeCategoryPARIObject)
        {
            
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryWithPARIInsert";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
               {
                this.Command.Parameters.Clear();

                SqlParameter chequeCategoryID = new SqlParameter();
                chequeCategoryID.ParameterName = "@ChequeCategoryID";
                chequeCategoryID.SqlDbType = SqlDbType.Int;
                chequeCategoryID.Direction = ParameterDirection.Input;
                chequeCategoryID.IsNullable = false;
                chequeCategoryID.Value = ChequeCategoryPARIObject.ChequeCategoryObject.ChequeCategoryID;
                this.Command.Parameters.Add(chequeCategoryID);

                SqlParameter ChequeNo = new SqlParameter();
                ChequeNo.ParameterName = "@ChequeNo";
                ChequeNo.SqlDbType = SqlDbType.BigInt;
                ChequeNo.Direction = ParameterDirection.Input;
                ChequeNo.IsNullable = false;
                ChequeNo.Value = ChequeCategoryPARIObject.PayAndReceiveItemObject.ChequeNo;
                this.Command.Parameters.Add(ChequeNo);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                //bankObject.BankID = Convert.ToInt32(this.Command.Parameters["@bankID"].Value);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for Update a object of BLL.BLLBank to table by ChequeNo
        /// this method check connection if connection is open Update object to table
        /// </summary>
        /// <param name="ChequeCategoryPARIObject" Type="BLL.BLLChequeCategoryWithPARI"></param>
        /// <param name="targetChequeNo" Type="long?"></param>
        /// <return>this type is void and Do not return</return>
        public void Update(BLL.BLLChequeCategoryWithPARI ChequeCategoryPARIObject, long? targetChequeNo)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryWithPARIUpdate";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter ChequeNo = new SqlParameter();
                ChequeNo.ParameterName = "@ChequeNo";
                ChequeNo.SqlDbType = SqlDbType.BigInt;
                ChequeNo.Direction = ParameterDirection.Input;
                ChequeNo.IsNullable = false;
                ChequeNo.Value = targetChequeNo;
                this.Command.Parameters.Add(ChequeNo);

                SqlParameter chequeCategoryID = new SqlParameter();
                chequeCategoryID.ParameterName = "@ChequeCategoryID";
                chequeCategoryID.SqlDbType = SqlDbType.NVarChar;
                chequeCategoryID.Direction = ParameterDirection.Input;
                chequeCategoryID.IsNullable = false;
                chequeCategoryID.Value = ChequeCategoryPARIObject.ChequeCategoryObject.ChequeCategoryID;
                this.Command.Parameters.Add(chequeCategoryID);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for Delete a row from table
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="chequeNo" Type="long"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(long chequeNo)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryWithPARIDeleteByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter chequNo = new SqlParameter();
                chequNo.ParameterName = "@ChequeNo";
                chequNo.SqlDbType = SqlDbType.BigInt;
                chequNo.Direction = ParameterDirection.Input;
                chequNo.IsNullable = false;
                chequNo.Value = chequeNo;
                this.Command.Parameters.Add(chequNo);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        public void Delete(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryWithPARIDeleteByField";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                this.Command.Parameters.Add(new SqlParameter("@fieldName", fieldName));
                this.Command.Parameters.Add(new SqlParameter("@value", value));

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <returns>result return to a list<> of BLL.BLLChequeCategoryWithPARI</returns>
        public List<BLL.BLLChequeCategoryWithPARI> FetchAll()
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryWithPARIFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();
                
                List<BLL.BLLChequeCategoryWithPARI> result = new List<BLL.BLLChequeCategoryWithPARI>();

                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="ChequeCategoryWithPARIDT" Type="DataTable"></param>
        /// <return>this type is void but result return to DataTable </return>
        public void FetchAll(DataTable ChequeCategoryWithPARIDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryWithPARIFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                sqlDataAdapter.Fill(ChequeCategoryWithPARIDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for FetchAll all rows from table by ChequeNo 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="chequeNo" Type="int"></param>
        /// <returns>return a object that type is BLL.BLLChequeCategoryWithPARI</returns>
        public BLL.BLLChequeCategoryWithPARI FetchByKey(int chequeNo)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryWithPARIFetchByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter pkChequeNo = new SqlParameter();
                pkChequeNo.ParameterName = "@ChequeNo";
                pkChequeNo.SqlDbType = SqlDbType.Int;
                pkChequeNo.Direction = ParameterDirection.Input;
                pkChequeNo.IsNullable = false;
                pkChequeNo.Value = chequeNo;
                this.Command.Parameters.Add(pkChequeNo);

                if (controlConnection)
                    this.BeginTransaction();

                BLL.BLLChequeCategoryWithPARI chequeCategoryPARIObject = new BLL.BLLChequeCategoryWithPARI();

                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    if (dataReader.Read())
                        InitialBusinessObjectsFromReader(chequeCategoryPARIObject, dataReader);
                    else
                        chequeCategoryPARIObject = null;
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (chequeCategoryPARIObject);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <returns>result return to a list<> of BLL.BLLChequeCategoryWithPARI</returns>
        public List<BLL.BLLChequeCategoryWithPARI> FetchByField(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspChequeCategoryWithPARIFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[ChequeCategoryWithPARI].[" + fieldName + "] = N'" + value.ToString() + "'"));

                List<BLL.BLLChequeCategoryWithPARI> result = new List<BLL.BLLChequeCategoryWithPARI>();

                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }
        
        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <param name="dataTable" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchByField(string fieldName, object value, DataTable dataTable)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryWithPARIFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[ChequeCategoryWithPARI].[" + fieldName + "] = N'" + value.ToString() + "'"));

                sqlDataAdapter.Fill(dataTable);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by condition 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="condition" Type="string"></param>
        /// <param name="ChequeCategoryPARI" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchAllByCondition(string condition, DataTable ChequeCategoryPARI)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspChequeCategoryWithPARIFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", condition));

                sqlDataAdapter.Fill(ChequeCategoryPARI);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>return a list<> that value is table columns value</returns>
        private List<BLL.BLLChequeCategoryWithPARI> InitialObjectsFromReader(IDataReader dataReader)
        {
            List<BLL.BLLChequeCategoryWithPARI> chequeCategoryPARIList = new List<BLL.BLLChequeCategoryWithPARI>();

            int ChequNoColumn = dataReader.GetOrdinal(BLL.BLLPayAndReceiveItem.PayAndReceiveItemField.ChequeNo.ToString());
            int ChequeCategoryIDColumn = dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.ChequeCategoryID.ToString());

            while (dataReader.Read())
            {
                BLL.BLLChequeCategoryWithPARI chequeCategoryPARIObject = new BLL.BLLChequeCategoryWithPARI();

                chequeCategoryPARIObject.ChequeNo = dataReader.GetInt64(ChequNoColumn);
                chequeCategoryPARIObject.ChequeCategoryID = dataReader.GetInt32(ChequeCategoryIDColumn);
                chequeCategoryPARIList.Add(chequeCategoryPARIObject);
            }
            return (chequeCategoryPARIList);
        }
        
        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="chequeCategoryPARIObject" Type="BLL.BLLChequeCategoryWithPARI"></param>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>this method is void but return a object of BLL.BLLBank that value is table columns value</returns>
        private void InitialBusinessObjectsFromReader(BLL.BLLChequeCategoryWithPARI chequeCategoryPARIObject, IDataReader dataReader)
        {
            chequeCategoryPARIObject.ChequeCategoryID = dataReader.GetInt32(dataReader.GetOrdinal(BLL.BLLChequeCategory.ChequeCategoryField.ChequeCategoryID.ToString()));
            chequeCategoryPARIObject.ChequeNo = dataReader.GetInt64(dataReader.GetOrdinal(BLL.BLLPayAndReceiveItem.PayAndReceiveItemField.ChequeNo.ToString()));
        }

        
    }
}
